
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetCurrentStudyStaff]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetCurrentStudyStaff]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*************************************************************************
* Name:			SetCurrentStudyStaff
* Purpose:		To be used by Human feed - updates Study Staff records as current or non-current
* Created:		idk
***************************************************************************/
CREATE PROCEDURE [dbo].[SetCurrentStudyStaff]

AS
BEGIN

	SET NOCOUNT ON;

	-- Set records non-current for the older versions of protocols
	Update Map 
	set IsCurrent = 0 
	from ProtocolStaff_Map map
	join Protocol p
		on map.ProtocolNumber = p.ProtocolNumber
		and p.IsCurrent = 1
	where map.ModuleType = 'H' and p.ModuleType = 'H'
	and map.RexProtocolId is not null
	and map.RexProtocolId <> p.RexProtocolId

	-- Mark the proper ones current.
	Update Map 
	set IsCurrent = 1 
	from ProtocolStaff_Map map
	join Protocol p
		on map.ProtocolNumber = p.ProtocolNumber
		and p.IsCurrent = 1
	where map.ModuleType = 'H' and p.ModuleType = 'H'
	and map.RexProtocolId is not null
	and map.RexProtocolId = p.RexProtocolId
	and  map.IsCurrent  = 0
	and map.RemovedProtocolProcessID is null


	DELETE FROM FwkResource_DomainUser_Map 
	WHERE Id IN 
		(
		SELECT FDMap.Id FROM FwkResource_DomainUser_Map FDMap 
			INNER JOIN AdmPerson Adm ON Adm.FwkDomainUserId = FDMap.FwkDomainUserId
			INNER JOIN Protocol P ON P.FwkResourceId = FDMap.FwkResourceId
			INNER JOIN ProtocolStaff_Map M ON M.ProtocolNumber = P.ProtocolNumber AND M.AdmPersonId = Adm.Id
		WHERE p.IsCurrent = 1 AND M.ModuleType = 'H' and P.ModuleType = 'H'
			and M.IsCurrent = 0
			and M.RexProtocolId is not null
			and M.AdmPersonId NOT IN 
				(Select M2.AdmPersonId
					from ProtocolSTaff_Map M2
					where M2.ProtocolNumber = P.ProtocolNumber 
						And M2.Iscurrent = 1)
		)

	IF @@ERROR <> 0 
	BEGIN
        RAISERROR('Unable to set Study Staff records as Current', 16, 1)
        RETURN @@ERROR
	END

	SET NOCOUNT OFF
END
GO
